Databases - ORMs

About

  • ORM (Object-Relational Mapping) is a programming technique that allows interaction between a relational database and an object-oriented programming language, so that the database tables and data are represented as objects in code.

  • The main objective of ORM is to simplify data manipulation, making the process of interacting with the database more intuitive and aligned with the principles of object-oriented programming, avoiding the direct use of SQL (Structured Query Language) for basic CRUD (Create, Read, Update, Delete) operations.

Functioning

  • Mapping Tables to Objects :

    • Each database table is mapped to a class in your programming language. Each row of the table becomes an instance of that class, and each column of the table becomes an attribute of the class.

  • CRUD Operations :

    • Instead of writing SQL queries manually to add, update, or query data in the database, you use the ORM's methods to do this. The ORM translates these methods into the corresponding SQL queries.

Advantages

  • Abstraction :

    • The ORM abstracts the complexity of SQL, allowing you to work with objects directly instead of writing manual SQL queries.

  • Error Reduction :

    • With ORM, you can avoid common SQL errors, such as SQL injection or syntax errors, because the ORM takes care of this automatically.

  • Portability :

    • Using ORM, the code can be more easily adapted to different databases, as most ORMs can connect to various database systems (MySQL, PostgreSQL, SQLite, etc.).

  • Performance :

    • Although not always faster than manual SQL, a well-configured ORM can optimize queries automatically, helping with performance.

  • Maintainability :

    • Coding becomes more organized, as the database manipulation code is centralized in the ORM and not in various parts of the system.

Disadvantages

  • Reading an article about not using ORMs .

    • Many of the disadvantages are explained, etc.

    • "Complex, redundant, slow", "just learn SQL", "treat SQL as an API".

  • Learning Curve :

    • For beginner developers, learning how to use an ORM can be more challenging than writing SQL directly.

  • Performance :

    • In some cases, the ORM's abstraction can generate less efficient SQL queries than those made manually, which can affect performance in complex operations.

  • Limitation of Advanced Features :

    • Some ORMs may not support advanced database-specific features or perform optimizations that an experienced developer would do directly in SQL.

Examples of ORMs

  • Python :

    • SQLAlchemy, Django ORM

  • Java :

    • Hibernate

  • Ruby :

    • ActiveRecord (Ruby on Rails)

  • PHP :

    • Doctrine

Entity Framework Core

  • Open-source.

Godot

DrizzleORM

Prisma

  • A modern ORM, focused on productivity.

  • For TypeScript  and JavaScript .

  • Support for automatic migrations .

  • Typed code generation (TypeScript).

  • Powerful and simplified queries.

Example 
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

async function main() {
    await prisma.personagem.create({
        data: {
            nome: 'Guerreiro',
            ouro: 1000
        }
    });
}

main();

Mongoose

SQLX

  • It is a library for Rust  that offers a safe and efficient way to interact with relational databases.

  • Unlike other database libraries in Rust, such as diesel , which use an ORM (Object-Relational Mapping) approach, sqlx  offers a more flexible SQL-based approach, but with additional features that make working with databases simpler and safer.

Characteristics

  • No ORM :

    • sqlx  is not a complete ORM, but rather a query builder  and database driver library. It provides an interface that allows you to write SQL directly, but in a safe and optimized way, with support for prepared statements and compile-time type checking.

  • Async and Sync :

    • sqlx  supports asynchronous and synchronous operations. It is optimized for use in asynchronous environments, such as applications based on async / await , but can also be used synchronously if needed.

  • Compatibility with Various Databases :

    • sqlx  supports multiple database management systems (DBMSs), including:

      • PostgreSQL

      • MySQL

      • SQLite

  • SQL Verification at Compile Time :

    • One of the great advantages of sqlx  is that it can verify SQL queries during compilation (if configured correctly), avoiding common SQL errors that would only be found at runtime. This improves the security and reliability of the code.

  • Query Preparation :

    • sqlx  allows the creation of 'prepared statements', which improves performance and protects against SQL injection attacks, in addition to providing better parameter management.

  • Integration with Migrations :

    • sqlx  offers native support for database migrations, making it easier to manage database versions throughout the project lifecycle.